﻿Imports System.Collections.Generic
Imports System.Data
Imports System.IO
Imports System.Web
Imports NPOI
Imports NPOI.HPSF
Imports NPOI.HSSF
Imports NPOI.HSSF.UserModel
Imports NPOI.POIFS
Imports NPOI.Util

Public Class DataTableRenderToExcel
	Public Shared Function RenderDataTableToExcel(SourceTable As DataTable) As Stream
		Dim workbook As New HSSFWorkbook()
		Dim ms As New MemoryStream()
		Dim sheet As HSSFSheet = DirectCast(workbook.CreateSheet(), HSSFSheet)
		Dim headerRow As HSSFRow = DirectCast(sheet.CreateRow(0), HSSFRow)

		' handling header.
		For Each column As DataColumn In SourceTable.Columns
			headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName)
		Next

		' handling value.
		Dim rowIndex As Integer = 1

		For Each row As DataRow In SourceTable.Rows
			Dim dataRow As HSSFRow = DirectCast(sheet.CreateRow(rowIndex), HSSFRow)
			Dim tmp As Double
			For Each column As DataColumn In SourceTable.Columns
				If column.DataType Is System.Type.[GetType]("System.Decimal") Then
					Try
						tmp = Double.Parse(row(column).ToString())
						dataRow.CreateCell(column.Ordinal).SetCellValue(tmp)
					Catch
						dataRow.CreateCell(column.Ordinal).SetCellValue("")



					End Try
				Else
					dataRow.CreateCell(column.Ordinal).SetCellValue(row(column).ToString())
				End If
			Next

			rowIndex += 1
		Next

		workbook.Write(ms)
		ms.Flush()
		ms.Position = 0

		sheet = Nothing
		headerRow = Nothing
		workbook = Nothing

		Return ms
	End Function

	Public Shared Sub RenderDataTableToExcel(SourceTable As DataTable, FileName As String)
		Dim ms As MemoryStream = TryCast(RenderDataTableToExcel(SourceTable), MemoryStream)
		Dim fs As New FileStream(FileName, FileMode.Create, FileAccess.Write)
		Dim data As Byte() = ms.ToArray()

		fs.Write(data, 0, data.Length)
		fs.Flush()
		fs.Close()

		data = Nothing
		ms = Nothing
		fs = Nothing
	End Sub

	Public Shared Function RenderDataTableFromExcel(ExcelFileStream As Stream, SheetName As String, HeaderRowIndex As Integer) As DataTable
		Dim workbook As New HSSFWorkbook(ExcelFileStream)
		Dim sheet As HSSFSheet = DirectCast(workbook.GetSheet(SheetName), HSSFSheet)

		Dim table As New DataTable()

		Dim headerRow As HSSFRow = DirectCast(sheet.GetRow(HeaderRowIndex), HSSFRow)
		Dim cellCount As Integer = headerRow.LastCellNum

		For i As Integer = headerRow.FirstCellNum To cellCount - 1
			Dim column As New DataColumn(headerRow.GetCell(i).StringCellValue)
			table.Columns.Add(column)
		Next

		Dim rowCount As Integer = sheet.LastRowNum

		For i As Integer = (sheet.FirstRowNum + 1) To sheet.LastRowNum - 1
			Dim row As HSSFRow = DirectCast(sheet.GetRow(i), HSSFRow)
			Dim dataRow As DataRow = table.NewRow()

			For j As Integer = row.FirstCellNum To cellCount - 1
                dataRow(j) = row.GetCell(j)
            Next
        Next

        ExcelFileStream.Close()
        workbook = Nothing
        sheet = Nothing
        Return table
	End Function

	Public Shared Function RenderDataTableFromExcel(ExcelFileStream As Stream, SheetIndex As Integer, HeaderRowIndex As Integer) As DataTable
		Dim workbook As New HSSFWorkbook(ExcelFileStream)
		Dim sheet As HSSFSheet = DirectCast(workbook.GetSheetAt(SheetIndex), HSSFSheet)

		Dim table As New DataTable()

		Dim headerRow As HSSFRow = DirectCast(sheet.GetRow(HeaderRowIndex), HSSFRow)
		Dim cellCount As Integer = headerRow.LastCellNum

		For i As Integer = headerRow.FirstCellNum To cellCount - 1
			Dim column As New DataColumn(headerRow.GetCell(i).StringCellValue)
			table.Columns.Add(column)
		Next

		Dim rowCount As Integer = sheet.LastRowNum

		For i As Integer = (sheet.FirstRowNum + 1) To sheet.LastRowNum - 1
			Dim row As HSSFRow = DirectCast(sheet.GetRow(i), HSSFRow)
			Dim dataRow As DataRow = table.NewRow()

			For j As Integer = row.FirstCellNum To cellCount - 1
				If row.GetCell(j) IsNot Nothing Then
                    dataRow(j) = row.GetCell(j)
                End If
			Next

			table.Rows.Add(dataRow)
		Next

		ExcelFileStream.Close()
		workbook = Nothing
		sheet = Nothing
		Return table
	End Function
End Class